ASP.NET Core Blazor | CRUD Operations

This is Part 19 of Web development with Blazor video series. In this video we will discuss how to implement all the CRUD operations i.e Create, Read, Update and Delete in ASP.NET Core Blazor.

In our previous video, Part 18 we discussed how to enable support for CRUD operations in a DataGrid. Please watch it before proceeding. 

Enable DataGrid CRUD operations

1. Specify the primary key column by setting IsPrimaryKey property to true.
2. In this example, EmployeeId column is marked as the primary key.
3. On <GridEditSettings> component, set AllowAdding="true", AllowEditing="true" and AllowDeleting="true".

@page "/gridedit"
@using Syncfusion.Blazor.Grids
@using Syncfusion.Blazor.DropDowns

<div style="width:750px">
    <SfGrid DataSource="@Employees" AllowPaging="true"
            Toolbar="@(new List<string>() { "Add", "Edit", "Update", "Delete", "Cancel" })">
        <GridEditSettings AllowAdding="true" AllowEditing="true" AllowDeleting="true"></GridEditSettings>
        <GridPageSettings PageSize="5"></GridPageSettings>
        <GridColumns>
            <GridColumn IsPrimaryKey="true" Field=@nameof(Employee.EmployeeId) HeaderText="ID" Width="40px">
            </GridColumn>
            <GridColumn Field=@nameof(Employee.FirstName) HeaderText="First Name"></GridColumn>
            <GridColumn Field=@nameof(Employee.LastName) HeaderText=" Last Name"></GridColumn>
            <GridColumn Field=@nameof(Employee.DateOfBrith) Format="d" HeaderText="Date of Birth">
            </GridColumn>
            <GridColumn Field="Department.DepartmentName" HeaderText="Department" Width="140px">
                <EditTemplate>
                    <SfDropDownList DataSource="@Departments" TItem="Department" TValue="int"
                                    @bind-Value="@((context as Employee).DepartmentId)">
                        <DropDownListFieldSettings Text="DepartmentName" Value="DepartmentId">
                        </DropDownListFieldSettings>
                    </SfDropDownList>
                </EditTemplate>
            </GridColumn>
            <GridColumn Field=@nameof(Employee.Gender) HeaderText="Gender" Width="140px">
                <EditTemplate>
                    <SfDropDownList DataSource="@GenderEnumValues" TItem="string" TValue="Gender"
                                    @bind-Value="@((context as Employee).Gender)">
                    </SfDropDownList>
                </EditTemplate>
            </GridColumn>
            <GridColumn Field=@nameof(Employee.Email) HeaderText="Email"></GridColumn>
        </GridColumns>
    </SfGrid>
</div>

@code{

    public List<Employee> Employees { get; set; }

    public List<Department> Departments { get; set; }

    public string[] GenderEnumValues { get; set; } = Enum.GetNames(typeof(Gender));

    [Inject]
    public IEmployeeService EmployeeService { get; set; }

    [Inject]
    public IDepartmentService DepartmentService { get; set; }

    protected override async Task OnInitializedAsync()
    {
        Employees = (await EmployeeService.GetAllEmployees()).ToList();
        Departments = (await DepartmentService.GetAllDepartments()).ToList();
    }
}

Set Toolbar property on the datagrid to display the toolbar icons (Add, Edit, Update, Delete and Cancel)

<SfGrid DataSource="@Employees" AllowPaging="true"
        Toolbar="@(new List<string>() { "Add", "Edit", "Update", "Delete", "Cancel" })">

• You can edit a row either by double-clicking on the row or by selecting the row and then click on the Edit button in the toolbar.
• To add a new record click on the Add button in the toolbar.
• To Save and Cancel use the the respective buttons in the toolbar.
• In Gender and Department columns we have a DropDownList inside the <EditTemplate>.
• This means we add a new or edit existing employee row, we get DropDownList as the Edit interface.
• We discussed all these in our previous video Part 18.

Saving CRUD operations in the Database

• EmployeeId is an identity column and the database will provide a value for this column when a new record is inserted.
• Set AllowAdding=false on EmployeeId <GridColumn> so the field is disabled and cannot be modified.
• We are binding the data to the datagrid component using DataSource property.
• This means the CRUD operations are only performed in memory at the datagrid level, they are not persisted to the database.
• Handle datagrid action events to save the changes in the underlying database table.
• When a datagrid action like Add, Update or Delete is initiated, OnActionBegin event is triggered.
• Handle OnActionBegin event to persist the changes in the database.
• For both the operations (Add and Update), RequestType is Save and for Delete it is Delete.
• Use Args.Action property to differentiate between Add and Update.
• @ref is included on the datagrid, so we can reference the grid and call Refresh() method after a new record is added.

@page "/gridedit"
@using Syncfusion.Blazor.Grids

<div style="width:800px">
    <SfGrid @ref="employeeGrid" DataSource="@Employees" AllowPaging="true"
            Toolbar="@(new List<string>() { "Add", "Edit", "Update", "Delete", "Cancel" })">
        <GridEditSettings AllowAdding="true" AllowEditing="true" AllowDeleting="true"></GridEditSettings>
        <GridPageSettings PageSize="5"></GridPageSettings>
        <GridEvents OnActionBegin="ActionBeginHandler" TValue="Employee">
        </GridEvents>
        <GridColumns>
            <GridColumn IsPrimaryKey="true" Field=@nameof(Employee.EmployeeId) HeaderText="ID" Width="60px">
            </GridColumn>
            @*Rest of the grid columns*@
        </GridColumns>
    </SfGrid>
</div>

@code{

    public List<Employee> Employees { get; set; }

    public SfGrid<Employee> employeeGrid { get; set; }

    [Inject]
    public IEmployeeService EmployeeService { get; set; }

    protected override async Task OnInitializedAsync()
    {
        Employees = (await EmployeeService.GetAllEmployees()).ToList();
    }

    public async void ActionBeginHandler(ActionEventArgs<Employee> Args)
    {
        if (Args.RequestType.Equals(Syncfusion.Blazor.Grids.Action.Save))
        {
            if (Args.Action == "Add")
            {
                await EmployeeService.AddEmployee(Args.Data);
                Employees = (await EmployeeService.GetAllEmployees()).ToList();
                employeeGrid.Refresh();
            }
            else
            {
                await EmployeeService.UpdateEmployee(Args.Data);
            }
        }
        if (Args.RequestType.Equals(Syncfusion.Blazor.Grids.Action.Delete))
        {
            await EmployeeService.DeleteEmployee(Args.Data.EmployeeId);
        }
    }
}

IEmployeeService

public interface IEmployeeService
{
    Task<IEnumerable<Employee>> Search(string name, Gender? gender);
    Task<IEnumerable<Employee>> GetAllEmployees();
    Task<EmployeeDataResult> GetEmployees(int skip, int take, string orderBy);
    Task<Employee> GetEmployee(int employeeId);
    Task<Employee> GetEmployeeByEmail(string email);
    Task<Employee> AddEmployee(Employee employee);
    Task<Employee> UpdateEmployee(Employee employee);
    Task DeleteEmployee(int employeeId);
}

EmployeeService

public class EmployeeService : IEmployeeService
{
    private readonly HttpClient httpClient;

    public EmployeeService(HttpClient httpClient)
    {
        this.httpClient = httpClient;
    }

    public async Task<IEnumerable<Employee>> GetAllEmployees()
    {
        return await httpClient.GetFromJsonAsync<IEnumerable<Employee>>("/api/employees/all");
    }

    public async Task<Employee> AddEmployee(Employee employee)
    {
        var response = await httpClient.PostAsJsonAsync<Employee>("/api/employees", employee);
        return await response.Content.ReadFromJsonAsync<Employee>();
    }

    public async Task DeleteEmployee(int employeeId)
    {
        await httpClient.DeleteAsync($"/api/employees/{employeeId}");
    }

    public Task<Employee> GetEmployee(int employeeId)
    {
        throw new NotImplementedException();
    }

    public Task<Employee> GetEmployeeByEmail(string email)
    {
        throw new NotImplementedException();
    }

    public async Task<EmployeeDataResult> GetEmployees(int skip, int take, string orderBy)
    {
        return await httpClient.GetFromJsonAsync<EmployeeDataResult>
            ($"/api/employees?skip={skip}&take={take}&orderBy={orderBy}");
    }

    public Task<IEnumerable<Employee>> Search(string name, Gender? gender)
    {
        throw new NotImplementedException();
    }

    public async Task<Employee> UpdateEmployee(Employee employee)
    {
        var response = await httpClient
            .PutAsJsonAsync<Employee>($"/api/employees/{employee.EmployeeId}", employee);
        return await response.Content.ReadFromJsonAsync<Employee>();
    }
}

EmployeesController

[Route("api/[controller]")]
[ApiController]
public class EmployeesController : ControllerBase
{
    private readonly IEmployeeRepository employeeRepository;

    public EmployeesController(IEmployeeRepository employeeRepository)
    {
        this.employeeRepository = employeeRepository;
    }

    [HttpGet("all")]
    public async Task<ActionResult> GetAllEmployees()
    {
        try
        {
            return Ok(await employeeRepository.GetAllEmployees());
        }
        catch (Exception)
        {
            return StatusCode(StatusCodes.Status500InternalServerError,
                "Error retrieving data from the database");
        }
    }

    [HttpGet("search")]
    public async Task<ActionResult<IEnumerable<Employee>>> Search(string name, Gender? gender)
    {
        try
        {
            var result = await employeeRepository.Search(name, gender);

            if (result.Any())
            {
                return Ok(result);
            }

            return NotFound();
        }
        catch (Exception)
        {
            return StatusCode(StatusCodes.Status500InternalServerError,
            "Error retrieving data from the database");
        }
    }

    [HttpGet]
    public async Task<ActionResult> GetEmployees(int skip = 0, int take = 5, string orderBy = "EmployeeId")
    {
        try
        {
            return Ok(await employeeRepository.GetEmployees(skip, take, orderBy));
        }
        catch (Exception)
        {
            return StatusCode(StatusCodes.Status500InternalServerError,
                "Error retrieving data from the database");
        }
    }

    [HttpGet("{id:int}")]
    public async Task<ActionResult<Employee>> GetEmployee(int id)
    {
        try
        {
            var result = await employeeRepository.GetEmployee(id);

            if (result == null)
            {
                return NotFound();
            }

            return result;
        }
        catch (Exception)
        {
            return StatusCode(StatusCodes.Status500InternalServerError,
                "Error retrieving data from the database");
        }
    }

    [HttpPost]
    public async Task<ActionResult<Employee>> CreateEmployee(Employee employee)
    {
        try
        {
            if (employee == null)
                return BadRequest();

            var emp = await employeeRepository.GetEmployeeByEmail(employee.Email);

            if(emp != null)
            {
                ModelState.AddModelError("Email", "Employee email already in use");
                return BadRequest(ModelState);
            }

            var createdEmployee = await employeeRepository.AddEmployee(employee);

            return CreatedAtAction(nameof(GetEmployee),
                new { id = createdEmployee.EmployeeId }, createdEmployee);
        }
        catch (Exception ex)
        {
            return StatusCode(StatusCodes.Status500InternalServerError,
                "Error creating new employee record");
        }
    }

    [HttpPut("{id:int}")]
    public async Task<ActionResult<Employee>> UpdateEmployee(int id, Employee employee)
    {
        try
        {
            if (id != employee.EmployeeId)
                return BadRequest("Employee ID mismatch");

            var employeeToUpdate = await employeeRepository.GetEmployee(id);

            if (employeeToUpdate == null)
            {
                return NotFound($"Employee with Id = {id} not found");
            }

            return await employeeRepository.UpdateEmployee(employee);
        }
        catch (Exception ex)
        {
            return StatusCode(StatusCodes.Status500InternalServerError,
                "Error updating employee record");
        }
    }

    [HttpDelete("{id:int}")]
    public async Task<ActionResult> DeleteEmployee(int id)
    {
        try
        {
            var employeeToDelete = await employeeRepository.GetEmployee(id);

            if (employeeToDelete == null)
            {
                return NotFound($"Employee with Id = {id} not found");
            }

            await employeeRepository.DeleteEmployee(id);

            return Ok($"Employee with Id = {id} deleted");
        }
        catch (Exception ex)
        {
            return StatusCode(StatusCodes.Status500InternalServerError,
                "Error deleting employee record");
        }
    }
}

EmployeeRepository

public class EmployeeRepository : IEmployeeRepository
{
    private readonly AppDbContext appDbContext;
    private readonly IDepartmentRepository departmentRepository;

    public EmployeeRepository(AppDbContext appDbContext, IDepartmentRepository departmentRepository)
    {
        this.appDbContext = appDbContext;
        this.departmentRepository = departmentRepository;
    }

    public async Task<IEnumerable<Employee>> GetAllEmployees()
    {
        return await appDbContext.Employees.Include(e => e.Department).ToListAsync();
    }

    public async Task<Employee> AddEmployee(Employee employee)
    {
        if (employee.DepartmentId == 0)
        {
            throw new Exception("Employee DepartmentId cannot be ZERO");
        }
        else
        {
            Department department = await this.departmentRepository
                .GetDepartment(employee.DepartmentId);
            if(department == null)
            {
                throw new Exception($"Invalid Employee DepartmentId {employee.DepartmentId}");
            }
            employee.Department = department;
        }

        var result = await appDbContext.Employees.AddAsync(employee);
        await appDbContext.SaveChangesAsync();
        return result.Entity;
    }

    public async Task DeleteEmployee(int employeeId)
    {
        var result = await appDbContext.Employees
            .FirstOrDefaultAsync(e => e.EmployeeId == employeeId);

        if (result != null)
        {
            appDbContext.Employees.Remove(result);
            await appDbContext.SaveChangesAsync();
        }
    }

    public async Task<Employee> GetEmployee(int employeeId)
    {
        return await appDbContext.Employees
            .Include(e => e.Department)
            .FirstOrDefaultAsync(e => e.EmployeeId == employeeId);
    }

    public async Task<Employee> GetEmployeeByEmail(string email)
    {
        return await appDbContext.Employees
            .FirstOrDefaultAsync(e => e.Email == email);
    }

    public async Task<EmployeeDataResult> GetEmployees
        (int skip = 0, int take = 5, string orderBy = "EmployeeId")
    {
        EmployeeDataResult result = new EmployeeDataResult()
        {
            Employees = appDbContext.Employees.OrderBy(orderBy).Skip(skip).Take(take),
            Count = await appDbContext.Employees.CountAsync()
        };

        return result;
    }

    public async Task<IEnumerable<Employee>> Search(string name, Gender? gender)
    {
        IQueryable<Employee> query = appDbContext.Employees;

        if (!string.IsNullOrEmpty(name))
        {
            query = query.Where(e => e.FirstName.Contains(name)
                        || e.LastName.Contains(name));
        }

        if (gender != null)
        {
            query = query.Where(e => e.Gender == gender);
        }

        return await query.ToListAsync();
    }

    public async Task<Employee> UpdateEmployee(Employee employee)
    {
        var result = await appDbContext.Employees
            .FirstOrDefaultAsync(e => e.EmployeeId == employee.EmployeeId);

        if (result != null)
        {
            result.FirstName = employee.FirstName;
            result.LastName = employee.LastName;
            result.Email = employee.Email;
            result.DateOfBrith = employee.DateOfBrith;
            result.Gender = employee.Gender;
            if (employee.DepartmentId != 0)
            {
                result.DepartmentId = employee.DepartmentId;
            }
            else if (employee.Department != null)
            {
                result.DepartmentId = employee.Department.DepartmentId;
            }
            result.PhotoPath = employee.PhotoPath;

            await appDbContext.SaveChangesAsync();

            return result;
        }

        return null;
    }
}